package bdsimple.impl.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bdsimple.abstractfactory.AbstractFactoryDAO;
import bdsimple.dao.base.ItemDAO;
import bdsimple.dao.base.TarjetaMemoriaAvanzadaDAO;
import bdsimple.dao.base.TarjetaMemoriaDAO;
import bdsimple.dao.base.TarjetaMemoriaIntermediaDAO;
import bdsimple.dao.base.TarjetaMemoriaSimpleDAO;
import bdsimple.dto.base.ItemDTO;
import bdsimple.dto.base.TarjetaMemoriaAvanzadaDTO;
import bdsimple.dto.base.TarjetaMemoriaDTO;
import bdsimple.dto.base.TarjetaMemoriaIntermediaDTO;
import bdsimple.dto.base.TarjetaMemoriaSimpleDTO;
import bdsimple.dto.base.VampiroDTO;
import dao.base.api.IDTO;
import dao.base.impl.Reference;

public class TarjetaMemoriaDAOImpl extends MySQLBaseDAO implements TarjetaMemoriaDAO {

	public TarjetaMemoriaDAOImpl() {
		super(TarjetaMemoriaDTO.class);
	}
	
	@Override
	protected String createTableColumns() //
			throws Exception {

		StringBuffer strbuf = new StringBuffer();
		
	    ItemDAO itemDAO = (ItemDAO) //
	    AbstractFactoryDAO.getFactoryDAO().getDAO( //
	        ItemDAO.class, connectionBean);

		strbuf.append(TarjetaMemoriaDTOImpl.ID);
		strbuf.append(" INT PRIMARY KEY AUTO_INCREMENT, ");
		strbuf.append(TarjetaMemoriaDTOImpl.NOMBRE);
		strbuf.append(" VARCHAR(50), ");
		strbuf.append(TarjetaMemoriaDTOImpl.DESCRIPCION);
		strbuf.append(" VARCHAR(200),  ");
		strbuf.append(TarjetaMemoriaDTOImpl.ITEM_REFERENCE);
	    strbuf.append(" INT REFERENCES   ");
	    strbuf.append(itemDAO.getTableName());

		return strbuf.toString();
	}

	@Override
	protected String createInsertCollst(IDTO dto) //
			throws Exception {

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(TarjetaMemoriaDTOImpl.NOMBRE);
		strbuf.append(", ");
		strbuf.append(TarjetaMemoriaDTOImpl.DESCRIPCION);
		strbuf.append(", ");
		strbuf.append(TarjetaMemoriaDTOImpl.ITEM_REFERENCE);

		return strbuf.toString();
	}

	@Override
	protected String createInsertValues(IDTO dto) //
			throws Exception {

		TarjetaMemoriaDTOImpl tarjetaMemoriaDTOImpl = (TarjetaMemoriaDTOImpl) dto;

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(singleQuotes(tarjetaMemoriaDTOImpl.getNombre()));
		strbuf.append(", ");
		strbuf.append(singleQuotes(tarjetaMemoriaDTOImpl.getDescripcion()));
		strbuf.append(", ");
		
		Reference<ItemDTO> ref = tarjetaMemoriaDTOImpl.getItemRef();
	    ref.checkInsert();
	    strbuf.append(ref.getIdAsString());

		return strbuf.toString();
	}


	protected String createUpdateValues(IDTO dto) //
			throws Exception {

		TarjetaMemoriaDTOImpl tarjetaMemoriaDTOImpl = (TarjetaMemoriaDTOImpl) dto;

		StringBuffer strbuf = new StringBuffer();

		strbuf.append(TarjetaMemoriaDTO.NOMBRE);
		strbuf.append(" = ");
		strbuf.append(singleQuotes(tarjetaMemoriaDTOImpl.getNombre()));

		strbuf.append(", ");

		strbuf.append(TarjetaMemoriaDTOImpl.DESCRIPCION);
		strbuf.append(" = ");
		strbuf.append(singleQuotes(tarjetaMemoriaDTOImpl.getDescripcion()));
		
		strbuf.append(", ");
		
		strbuf.append(TarjetaMemoriaDTOImpl.ITEM_REFERENCE);
		strbuf.append(" = ");
		Reference<ItemDTO> ref = tarjetaMemoriaDTOImpl.getItemRef();
	    ref.checkUpdate();
	    strbuf.append(ref.getIdAsString());

		return strbuf.toString();
	}
	
	protected TarjetaMemoriaDTOImpl resultSetToDTO(ResultSet rs) throws Exception {

		TarjetaMemoriaDTOImpl ret = (TarjetaMemoriaDTOImpl) dtaSession.getDtaByKey( //
				TarjetaMemoriaDTOImpl.class, rs.getInt(TarjetaMemoriaDTO.ID));

		if (ret != null) {
			return ret;
		}
		
		//---------------------------------------------------------------------------------
		
//		 Reference<ItemDTO> ref = ret.getItemRef();
//		 ref.setRefIdent(rs.getInt(TarjetaMemoriaDTOImpl.ITEM_REFERENCE));
//		 ref.setRefValue(null);
//				
		//---------------------------------------------------------------------------------
		if (rs.getString("dtoClass").equals(TarjetaMemoriaSimpleDTO.class.getSimpleName())) {
			TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaSimpleDAO.class, connectionBean);

			ret = (TarjetaMemoriaDTOImpl) tarjetaMemoriaSimpleDAO.loadById( //
					rs.getInt(TarjetaMemoriaDTOImpl.ID));

			return (TarjetaMemoriaDTOImpl) dtaSession.add(ret);
		}
		//---------------------------------------------------------------------------------
		if (rs.getString("dtoClass").equals(TarjetaMemoriaIntermediaDTO.class.getSimpleName())) {
			TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaIntermediaDAO.class, connectionBean);

			ret = (TarjetaMemoriaDTOImpl) tarjetaMemoriaIntermediaDAO.loadById( //
					rs.getInt(TarjetaMemoriaDTOImpl.ID));

			return (TarjetaMemoriaDTOImpl) dtaSession.add(ret);
		}
		//---------------------------------------------------------------------------------
		if (rs.getString("dtoClass").equals(TarjetaMemoriaAvanzadaDTO.class.getSimpleName())) {
			TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaAvanzadaDAO.class, connectionBean);

			ret = (TarjetaMemoriaDTOImpl) tarjetaMemoriaAvanzadaDAO.loadById( //
					rs.getInt(TarjetaMemoriaDTOImpl.ID));

			return (TarjetaMemoriaDTOImpl) dtaSession.add(ret);
		}
		//---------------------------------------------------------------------------------

		
		throw new IllegalArgumentException(rs.getString("dtoClass"));
	}

	@Override
	protected TarjetaMemoriaDTOImpl internalResultSetToDTO(ResultSet rs, IDTO dto)
			throws Exception {
		
		TarjetaMemoriaDTOImpl ret = (TarjetaMemoriaDTOImpl) dto;

	    ret.setId/*         */(rs.getInt(TarjetaMemoriaDTOImpl.ID));
	    ret.setNombre/*     */(rs.getString(TarjetaMemoriaDTOImpl.NOMBRE));
	    ret.setDescripcion/**/(rs.getString(TarjetaMemoriaDTOImpl.DESCRIPCION));
	    
	    Reference<ItemDTO> ref = ret.getItemRef();
	    ref.setRefIdent(rs.getInt(TarjetaMemoriaDTOImpl.ITEM_REFERENCE));
	    ref.setRefValue(null);

	    return ret;
	}

	// --------------------------------------------------------------------------------
	// IDAO
	// --------------------------------------------------------------------------------

	@Override
	public void insert(IDTO dto) throws Exception {
		
		if (dto instanceof TarjetaMemoriaSimpleDTO) {
			TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaSimpleDAO.class, connectionBean);

			tarjetaMemoriaSimpleDAO.insert(dto);

			return;
		}

		if (dto instanceof TarjetaMemoriaIntermediaDTO) {
			TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaIntermediaDAO.class, connectionBean);

			tarjetaMemoriaIntermediaDAO.insert(dto);

			return;
		}
		
		if (dto instanceof TarjetaMemoriaAvanzadaDTO) {
			TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaAvanzadaDAO.class, connectionBean);

			tarjetaMemoriaAvanzadaDAO.insert(dto);

			return;
		}
		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// --------------------------------------------------------------------------------

	@Override
	public void update(IDTO dto) throws Exception {
		
		if (dto instanceof TarjetaMemoriaSimpleDTO) {
			TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaSimpleDAO.class, connectionBean);

			tarjetaMemoriaSimpleDAO.update(dto);

			return;
		}

		if (dto instanceof TarjetaMemoriaIntermediaDTO) {
			TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaIntermediaDAO.class, connectionBean);

			tarjetaMemoriaIntermediaDAO.update(dto);

			return;
		}
		
		if (dto instanceof TarjetaMemoriaAvanzadaDTO) {
			TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaAvanzadaDAO.class, connectionBean);

			tarjetaMemoriaAvanzadaDAO.update(dto);

			return;
		}
		
		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// --------------------------------------------------------------------------------

	@Override
	public void delete(IDTO dto) throws Exception {
		
		if (dto instanceof TarjetaMemoriaSimpleDTO) {
			TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaSimpleDAO.class, connectionBean);

			tarjetaMemoriaSimpleDAO.delete(dto);

			return;
		}

		if (dto instanceof TarjetaMemoriaIntermediaDTO) {
			TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaIntermediaDAO.class, connectionBean);

			tarjetaMemoriaIntermediaDAO.delete(dto);

			return;
		}
		
		if (dto instanceof TarjetaMemoriaAvanzadaDTO) {
			TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
			AbstractFactoryDAO.getFactoryDAO().getDAO( //
					TarjetaMemoriaAvanzadaDAO.class, connectionBean);

			tarjetaMemoriaAvanzadaDAO.delete(dto);

			return;
		}

		// ---------------------------------------------
		// Others if (dto instanceof xxxDTO) if required
		// ---------------------------------------------
	}

	// --------------------------------------------------------------------------------

	@Override
	public List<IDTO> listAll(int lim, int off) throws Exception {

		TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaSimpleDAO.class, connectionBean);

		TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaIntermediaDAO.class, connectionBean);
		
		TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaAvanzadaDAO.class, connectionBean);

		StringBuffer strbuf = new StringBuffer();

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaSimpleDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaSimpleDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaSimpleDAO.getTableName() + "." + TarjetaMemoriaSimpleDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);

		strbuf.append(" UNION ");

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaIntermediaDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaIntermediaDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaIntermediaDAO.getTableName() + "." + TarjetaMemoriaIntermediaDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);
		
		strbuf.append(" UNION ");

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaAvanzadaDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaAvanzadaDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaAvanzadaDAO.getTableName() + "." + TarjetaMemoriaAvanzadaDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);

		if (lim >= 0 && off >= 0) {
			strbuf.append(" LIMIT  ");
			strbuf.append(lim);
			strbuf.append(" OFFSET ");
			strbuf.append(off);
		}

		System.err.println(strbuf.toString());

		ResultSet rs = //
		connection.createStatement().executeQuery(strbuf.toString());

		List<IDTO> ret = new ArrayList<IDTO>();

		while (rs.next()) {
			ret.add(resultSetToDTO(rs));
		}

		return ret;
	}

	// --------------------------------------------------------------------------------

	@Override
	public List<IDTO> listBy(String key, Object val) throws Exception {
		
		TarjetaMemoriaSimpleDAO tarjetaMemoriaSimpleDAO = (TarjetaMemoriaSimpleDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaSimpleDAO.class, connectionBean);

		TarjetaMemoriaIntermediaDAO tarjetaMemoriaIntermediaDAO = (TarjetaMemoriaIntermediaDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaIntermediaDAO.class, connectionBean);
		
		TarjetaMemoriaAvanzadaDAO tarjetaMemoriaAvanzadaDAO = (TarjetaMemoriaAvanzadaDAO) //
		AbstractFactoryDAO.getFactoryDAO().getDAO( //
				TarjetaMemoriaAvanzadaDAO.class, connectionBean);

		StringBuffer strbuf = new StringBuffer();

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaSimpleDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaSimpleDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaSimpleDAO.getTableName() + "." + TarjetaMemoriaSimpleDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);
		strbuf.append(" AND ");
		strbuf.append(key);
		strbuf.append(" = ");
		strbuf.append(val);

		strbuf.append(" UNION ");

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaIntermediaDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaIntermediaDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaIntermediaDAO.getTableName() + "." + TarjetaMemoriaIntermediaDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);
		strbuf.append(" AND ");
		strbuf.append(key);
		strbuf.append(" = ");
		strbuf.append(val);
		
		strbuf.append(" UNION ");

		strbuf.append("SELECT " + getTableName() + ".*, '" + //
				TarjetaMemoriaAvanzadaDTO.class.getSimpleName() + "' AS dtoClass FROM ");

		strbuf.append(this.getTableName() + ", " + tarjetaMemoriaAvanzadaDAO.getTableName());
		strbuf.append(" WHERE  " + //
				tarjetaMemoriaAvanzadaDAO.getTableName() + "." + TarjetaMemoriaAvanzadaDTO.ID + //
				" = " + //
				this.getTableName() + "." + TarjetaMemoriaDTO.ID);
		strbuf.append(" AND ");
		strbuf.append(key);
		strbuf.append(" = ");
		strbuf.append(val);

		System.err.println(strbuf.toString());

		ResultSet rs = //
		connection.createStatement().executeQuery(strbuf.toString());

		List<IDTO> ret = new ArrayList<IDTO>();

		while (rs.next()) {
			ret.add(resultSetToDTO(rs));
		}

		return ret;
	}

	// --------------------------------------------------------------------------------
	
	public List<TarjetaMemoriaDTO> listAllTarjetaMemoria(int idVampiro) throws SQLException, Exception{
		
		TarjetaMemoriaVampiroDTOImpl tarjetaMemoriaVampiroDTOImpl = new TarjetaMemoriaVampiroDTOImpl(); 
		
		StringBuffer strbuf = new StringBuffer();
		
		strbuf.append("SELECT ");
        strbuf.append(getTableName() + ".*");
        strbuf.append(" FROM ");
        strbuf.append(getTableName());
        strbuf.append(" JOIN ");
        strbuf.append(singleQuotes(tarjetaMemoriaVampiroDTOImpl.getClass().getSimpleName()));
        strbuf.append(" AS dtoClass ");
        strbuf.append(" ON ");
        strbuf.append(getTableName() + "." + VampiroDTO.ID);
        strbuf.append(" = ");
        strbuf.append(idVampiro );

        System.err.println(strbuf.toString());
		
		ResultSet rs = //
			connection.createStatement().executeQuery(strbuf.toString());
		
		List<TarjetaMemoriaDTO> ret = new ArrayList<TarjetaMemoriaDTO>();
		
		while (rs.next()) {
			ret.add(resultSetToDTO(rs));
		}

		return ret;
	}
	
}
